import random
from datetime import time

from util.common import random_string, render_template, write_file, read_excel

if __name__ == '__main__':
    # "FAKE"

    df1 = read_excel("source/tf_a_asset_group.xlsx")
    df2 = read_excel("source/tf_smp_web_asset2.xlsx")

    group = df1[['ASSET_GROUP_ID', 'NAME']].values.tolist()

    result_dict = {}
    grouped = df2.groupby('ASSET_GROUP_ID')
    for group_name, group_data in grouped:
        names = group_data['ID'].tolist()
        result_dict[str(group_name)] = names

    # print(group)
    # print(result_dict)

    list = []
    with open('/report.txt', 'r', encoding='utf-8') as file:
        for line in file:
            fullname = line.strip()
            idCol, code, system, *_, name = fullname.split("_")
            reName, *_ = name.split(".")
            dateTime = idCol.replace("NL", "")[0: 8]
            reportName = system + '_' + reName
            id = "SEEPZX" + random_string(14)

            hour = random.randint(0, 23)
            minute = random.randint(0, 59)
            second = random.randint(0, 59)
            random_time = time(hour, minute, second)

            webAssetId = "NET07470125062331174"

            result = next((item[0] for item in group if str(system) in str(item[1])), None)
            if result:
                if str(result) in result_dict:
                    webAssetId = random.choice(result_dict[str(result)])
                    print(webAssetId)

            seep = {
                "seepHoleId": id,
                "seepReportId": id,
                "holeName": reName,
                "holeType": '30',
                "safetyLevel": '2',
                "affectUrl": 30,
                "cnnvd": None,
                "cnvd": None,
                "cveid": None,
                "publishTime": f"to_date('{dateTime}', 'yyyymmdd')",
                "publishOrg": None,
                "holeDesc": '',
                "reinforceAdvice": '',
                "process": None,
                "reform": None,
                "vulnId": None,
                "createTime": f"to_date('{dateTime} {random_time.strftime('%H:%M:%S')}', 'yyyymmdd hh24:mi:ss')",
                "createUser": 'PORT6452766823027939',
                "reCheck": None,
                "reportStatus": '1',
            }
            message1 = render_template("""
                INSERT INTO TF_SA_SEEP_HOLE(SEEP_HOLE_ID, SEEP_REPORT_ID, HOLE_NAME, HOLE_TYPE, SAFETY_LEVEL, AFFECT_URL, CNNVD, CNVD,
                                            CVEID, PUBLISH_TIME, PUBLISH_ORG, HOLE_DESC, REINFORCE_ADVICE, PROCESS, REFORM, VULN_ID,
                                            CREATE_TIME, CREATE_USER, RECHECK, REPORT_STATUS)
                VALUES ($seepHoleId,$seepReportId,$holeName,$holeType,$safetyLevel,$affectUrl,$cnnvd,$cnvd,$cveid,
                        $publishTime,$publishOrg,$holeDesc,$reinforceAdvice,$process,$reform,$vulnId,
                        $createTime,$createUser,$reCheck,$reportStatus);
            """, seep)

            list.append(message1)

            report = {
                "seepReportId": id,
                "reportName": reportName,
                "webAssetId": webAssetId,
                "testTime": f"to_date('{dateTime}', 'yyyymmdd')",
                "testUser": '洪礼坤',
                "reportStatus": '2',
                "createTime": f"to_date('{dateTime} {random_time.strftime('%H:%M:%S')}', 'yyyymmdd hh24:mi:ss')",
                "createUser": 'PORT6452766823027939',
                "systemType": '2'
            }

            message2 = render_template(
                """
                        INSERT INTO TF_SA_SEEP_REPORT (SEEP_REPORT_ID, REPORT_NAME, WEB_ASSET_ID, TEST_TIME, TEST_USER, REPORT_STATUS,
                                               CREATE_TIME, CREATE_USER, SYSTEM_TYPE)
                        VALUES ($seepReportId,$reportName,$webAssetId,$testTime,$testUser,$reportStatus,
                                $createTime,$createUser,$systemType);
                    """,
                report)
            list.append(message2)

            log = {
                "id": "USRLZX" + random_string(14),
                "userAccountId": "PORT2692645573055148",
                "userAccountName": "9990798",
                "userName": "9990798",
                "sessionId": "E5D410A2F03C769FF2E7D13FC231F1A3",
                "ip": "10.44.105.7",
                "port": None,
                "module": "渗透报告管理",
                "desc": "进入渗透报告管理列表页面成功",
                "level": 0,
                "levelDesc": "正常",
                "catagory": 3,
                "catagoryDesc": "业务操作管理",
                "subCatagory": 54,
                "subCatagoryDesc": "SEEPREPORT",
                "moduleCode": "SEEPREPORT",
                "optCode": "1",
                "result": 1,
                "opTime": f"to_date('{dateTime} {random_time.strftime('%H:%M:%S')}', 'yyyymmdd hh24:mi:ss')",
                "saveTime": f"to_date('{dateTime} {random_time.strftime('%H:%M:%S')}', 'yyyymmdd hh24:mi:ss')",
                "logDescMac": None,
            }

            message3 = render_template(
                """
                            INSERT INTO TL_LOG_USER (USER_LOG_ID,
                                 USER_ACCOUNT_ID,
                                 USER_ACCOUNT_NAME,
                                 USER_NAME,
                                 SESSION_ID,
                                 USER_IP,
                                 USER_PORT,
                                 MODULE_NAME,
                                 LOG_DESC,
                                 LOG_LEVEL,
                                 LOG_LEVEL_DESC,
                                 LOG_CATAGORY,
                                 LOG_CATAGORY_DESC,
                                 LOG_SUB_CATAGORY,
                                 LOG_SUB_CATAGORY_DESC,
                                 MOD_CODE,
                                 OP_CODE,
                                 OP_RESULT,
                                 OP_TIME,
                                 SAVE_TIME,
                                 LOG_DESC_MAC)
                            values ($id,
                                    $userAccountId,
                                    $userAccountName,
                                    $userName,
                                    $sessionId,
                                    $ip,
                                    $port,
                                    $module,
                                    $desc,
                                    $level,
                                    $levelDesc,
                                    $catagory,
                                    $catagoryDesc,
                                    $subCatagory,
                                    $subCatagoryDesc,
                                    $moduleCode,
                                    $optCode,
                                    $result,
                                    $opTime,
                                    $saveTime,
                                    $logDescMac
            );
            """, log)
            list.append(message3)

            file = {
                "fileUploadId": "FILEZX" + random_string(14),
                "fileName": fullname,
                "filePath": "/opt/satp/smp_template/policy/zx/" + fullname,
                "oid": id,
                "fileDesc": None,
                "fileType": None,
            }

            message4 = render_template(
                """
                            INSERT INTO TF_H_FILEUPLOAD (ID, FILE_NAME, FILE_PATH, OID, FILE_DESC, FILE_TYPE)
                            VALUES ($fileUploadId, $fileName, $filePath, $oid, $fileDesc,$fileType);
            """, file)
            list.append(message4)

    write_file('target/SATP_渗透报告.sql', list)
       
